﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;
namespace Human
{
	/// <summary>
	/// Summary description for frmBaoCaoThucLuc.
	/// </summary>
	public class frmBaoCaoThucLuc : System.Windows.Forms.Form
	{
		private AccessData  mDB=new AccessData();
		private DataSet ds =new DataSet();
		private DataSet dsxml =new DataSet();
		private frmReport  rpt;
		private ExportToExcel p =new ExportToExcel();
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.Button butKetThuc;
        private System.Windows.Forms.Button butBaoCao;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.RadioButton radMH;
		private System.Windows.Forms.RadioButton radMI;
		private System.Windows.Forms.RadioButton radExcel;
        private MaskedTextBox txtTungay;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public frmBaoCaoThucLuc()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmBaoCaoThucLuc));
            this.label3 = new System.Windows.Forms.Label();
            this.butKetThuc = new System.Windows.Forms.Button();
            this.butBaoCao = new System.Windows.Forms.Button();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.radMH = new System.Windows.Forms.RadioButton();
            this.radMI = new System.Windows.Forms.RadioButton();
            this.radExcel = new System.Windows.Forms.RadioButton();
            this.txtTungay = new System.Windows.Forms.MaskedTextBox();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.ForeColor = System.Drawing.Color.DarkBlue;
            this.label3.Location = new System.Drawing.Point(20, 18);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(71, 16);
            this.label3.TabIndex = 18;
            this.label3.Text = "Đến ngày :";
            // 
            // butKetThuc
            // 
            this.butKetThuc.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butKetThuc.ForeColor = System.Drawing.Color.DarkBlue;
            this.butKetThuc.Image = ((System.Drawing.Image)(resources.GetObject("butKetThuc.Image")));
            this.butKetThuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butKetThuc.Location = new System.Drawing.Point(127, 106);
            this.butKetThuc.Name = "butKetThuc";
            this.butKetThuc.Size = new System.Drawing.Size(85, 28);
            this.butKetThuc.TabIndex = 3;
            this.butKetThuc.Text = "Kết thúc";
            this.butKetThuc.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butKetThuc.UseVisualStyleBackColor = true;
            this.butKetThuc.Click += new System.EventHandler(this.butKetThuc_Click);
            // 
            // butBaoCao
            // 
            this.butBaoCao.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butBaoCao.ForeColor = System.Drawing.Color.DarkBlue;
            this.butBaoCao.Image = ((System.Drawing.Image)(resources.GetObject("butBaoCao.Image")));
            this.butBaoCao.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butBaoCao.Location = new System.Drawing.Point(36, 106);
            this.butBaoCao.Name = "butBaoCao";
            this.butBaoCao.Size = new System.Drawing.Size(85, 28);
            this.butBaoCao.TabIndex = 2;
            this.butBaoCao.Text = "Thực hiện";
            this.butBaoCao.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butBaoCao.UseVisualStyleBackColor = true;
            this.butBaoCao.Click += new System.EventHandler(this.butBaoCao_Click);
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.radMH);
            this.groupBox1.Controls.Add(this.radMI);
            this.groupBox1.Controls.Add(this.radExcel);
            this.groupBox1.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(0)))), ((int)(((byte)(160)))));
            this.groupBox1.Location = new System.Drawing.Point(15, 48);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(232, 44);
            this.groupBox1.TabIndex = 1;
            this.groupBox1.TabStop = false;
            // 
            // radMH
            // 
            this.radMH.AutoSize = true;
            this.radMH.Checked = true;
            this.radMH.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radMH.ForeColor = System.Drawing.Color.DarkBlue;
            this.radMH.Location = new System.Drawing.Point(8, 13);
            this.radMH.Name = "radMH";
            this.radMH.Size = new System.Drawing.Size(79, 20);
            this.radMH.TabIndex = 0;
            this.radMH.TabStop = true;
            this.radMH.Text = "Màn hình";
            this.radMH.KeyDown += new System.Windows.Forms.KeyEventHandler(this.radMH_KeyDown);
            // 
            // radMI
            // 
            this.radMI.AutoSize = true;
            this.radMI.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radMI.ForeColor = System.Drawing.Color.DarkBlue;
            this.radMI.Location = new System.Drawing.Point(88, 13);
            this.radMI.Name = "radMI";
            this.radMI.Size = new System.Drawing.Size(65, 20);
            this.radMI.TabIndex = 1;
            this.radMI.Text = "Máy In";
            this.radMI.KeyDown += new System.Windows.Forms.KeyEventHandler(this.radMI_KeyDown);
            // 
            // radExcel
            // 
            this.radExcel.AutoSize = true;
            this.radExcel.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radExcel.ForeColor = System.Drawing.Color.DarkBlue;
            this.radExcel.Location = new System.Drawing.Point(160, 13);
            this.radExcel.Name = "radExcel";
            this.radExcel.Size = new System.Drawing.Size(59, 20);
            this.radExcel.TabIndex = 1;
            this.radExcel.Text = "Excel";
            this.radExcel.KeyDown += new System.Windows.Forms.KeyEventHandler(this.radExcel_KeyDown);
            // 
            // txtTungay
            // 
            this.txtTungay.Font = new System.Drawing.Font("Arial", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtTungay.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(0)))), ((int)(((byte)(160)))));
            this.txtTungay.Location = new System.Drawing.Point(108, 15);
            this.txtTungay.Mask = "00/00/0000";
            this.txtTungay.Name = "txtTungay";
            this.txtTungay.Size = new System.Drawing.Size(81, 22);
            this.txtTungay.TabIndex = 19;
            this.txtTungay.ValidatingType = typeof(System.DateTime);
            this.txtTungay.Validated += new System.EventHandler(this.txbTuNgay_Validated);
            this.txtTungay.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txbTuNgay_KeyDown);
            // 
            // frmBaoCaoThucLuc
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(257, 142);
            this.Controls.Add(this.txtTungay);
            this.Controls.Add(this.groupBox1);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.butKetThuc);
            this.Controls.Add(this.butBaoCao);
            this.ForeColor = System.Drawing.Color.DarkBlue;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frmBaoCaoThucLuc";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Báo cáo thực lực";
            this.Load += new System.EventHandler(this.frmBaoCaoThucLuc_Load);
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void butKetThuc_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}

		private void print(int prn)
		{
            DataSet dstmp = new DataSet();
            ExportToExcel exp = new ExportToExcel();
            string s_mmyy = "";
			string sql="SELECT '"+txtTungay.Text+"' as ngaythl,count(l.manv) as ts_ld,sum(case when l.phai='Nữ' then 1 else 0 end) as nu_ld";			
			sql+=" , sum(case when hd.doan in ('R','r') then 1 else 0 end) as ts_doanv";
			sql+=" , sum(case when l.phai='Nữ' and hd.doan in ('R','r') then 1 else 0 end) as nu_doanv";
			sql+=" , sum(case when hd.dang in ('R','r') then 1 else 0 end) as ts_dangv";
			sql+=" , sum(case when l.phai='Nữ' and hd.dang in ('R','r') then 1 else 0 end) as nu_dangv";
			sql+=" , sum(case when l.matgiao in('01','02','03','04','05') and hd.cong_doan in ('R','r') then 1 else 0 end) as ts_dvctg";
			sql+=" , sum(case when l.matgiao='01' and hd.cong_doan in ('R','r') then 1 else 0 end) as dp_dvctg";
			sql+=" , sum(case when l.matgiao='02' and hd.cong_doan in ('R','r') then 1 else 0 end) as tc_dvctg";
			sql+=" , sum(case when l.matgiao='03' and hd.cong_doan in ('R','r') then 1 else 0 end) as tl_dvctg";
            sql+= " , sum(case when l.matgiao='04' and hd.cong_doan in ('R','r') then 1 else 0 end) as cd_dvctg";       
            sql += " ,to_char(now(),'dd') as ngay,to_char(now(),'mm') as thang,to_char(now(),'yyy') as nam";
            sql += " FROM " + mDB.user + ".llnv l," + mDB.user + ".hopdong hd";
			sql+=" WHERE l.manv=hd.manv and ( hd.ngaynghi is null or ";
            sql += " to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + txtTungay.Text.Trim() + "','dd/mm/yyyy'))";
            sql += " and hd.ngayhd < to_timestamp('" + txtTungay.Text.Trim() + "','dd/mm/yyyy')";			
			ds=mDB.get_data(sql);			
			ds.WriteXml("..\\xml\\BaoCaoM.xml",XmlWriteMode.WriteSchema);
            s_mmyy = txtTungay.Text.Trim().Substring(3, 2) + txtTungay.Text.Trim().Substring(8, 2);
			if (ds.Tables[0].Rows.Count>0)
			{
                switch (prn)
                {
                    case 2:
                        rpt = new frmReport(mDB,ds,"BÁO CÁO THỰC LỰC", "BaoCaoM.rpt" , true,0);
                        
                            break;
                    case 1: frmReport f = new frmReport(mDB, ds, "BÁO CÁO THỰC LỰC", "BaoCaoM.rpt", true, 0);
                            f.ShowDialog(this);
                            break;                        
                    case 3: dstmp = exp_exel(ds);
                            exp.exp_exel_bcthucluc(dstmp, " bao cao thuc luc", txtTungay.Text,s_mmyy);                      
                            break; 
                }             
                
			}
			else
				MessageBox.Show("Chưa có đầy đủ dữ liệu","Thông Báo");
		}
        public DataSet exp_exel(DataSet dse)
        {

            DataSet dse1 = new DataSet();
            string[] str = new string[] { "TSCĐ Bộ Phận", "HCVP", "TS Tổ CĐ", "HCVP", "CBUVBCHCĐ", "CBUVBCHCĐBP", "TSTrưởng Phó", "TSTP Nữ", "TS Lao Động", "LĐ Nữ", "TS Đoàn Viên", "ĐV Nữ", "TS Đảng Viên", "ĐV Nữ", "TS Tôn Giáo", "Phật Giáo", "Thiên Chúa", "Tin Lành", "Cao Đài", "Ghi Chú" };
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt",typeof(string));
            dse1.Tables[0].Columns.Add("ts_ld", typeof(string));
            dse1.Tables[0].Columns.Add("nu_ld", typeof(string));
            dse1.Tables[0].Columns.Add("ts_doanv", typeof(string));
            dse1.Tables[0].Columns.Add("nu_doanv", typeof(string));
            dse1.Tables[0].Columns.Add("ts_dangv", typeof(string));
            dse1.Tables[0].Columns.Add("nu_dangv", typeof(string));
            dse1.Tables[0].Columns.Add("ts_dvctg", typeof(string));
            dse1.Tables[0].Columns.Add("dp_dvctg", typeof(string));
            dse1.Tables[0].Columns.Add("tc_dvctg", typeof(string));
            dse1.Tables[0].Columns.Add("tl_dvctg", typeof(string));
            dse1.Tables[0].Columns.Add("cd_dvctg", typeof(string));           
            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables[0].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt.ToString();
                nrow["ts_ld"] = r["ts_ld"].ToString();
                nrow["nu_ld"] = r["nu_ld"].ToString();
                nrow["ts_doanv"] = r["ts_doanv"].ToString();
                nrow["nu_doanv"] = r["nu_doanv"].ToString();
                nrow["ts_dangv"] = r["ts_dangv"].ToString();
                nrow["nu_dangv"] = r["nu_dangv"].ToString();
                nrow["ts_dvctg"] = r["ts_dvctg"].ToString();
                nrow["dp_dvctg"] = r["dp_dvctg"].ToString();
                nrow["tc_dvctg"] = r["tc_dvctg"].ToString();
                nrow["tl_dvctg"] = r["tl_dvctg"].ToString();
                nrow["cd_dvctg"] = r["cd_dvctg"].ToString();             
                dse1.Tables[0].Rows.Add(nrow);
            }
            return dse1;
        }
		private int kiemtrangay(int dtemp,int y)
		{
			int d=0;
			
			switch(dtemp)
			{
				case 1:
				case 3:
				case 5:
				case 7:
				case 8:
				case 10:
				case 12:
					d=31;
					break;
				case 2:
					if((y%4)==0)
						d=29;
					else if((y%4)!=0)
						d=28;
					break;
				case 4:
				case 6:
				case 9:
				case 11:
					d=30;
					break;
			}
			return d;
		}
		private void txbTuNgay_Validated(object sender, System.EventArgs e)
		{		
            txtTungay.Text = txtTungay.Text.Trim();
            if (txtTungay.Text == "  /  /")
            {
                return;
            }
            if (!mDB.kiemtrangayhople(txtTungay.Text))
            {
                MessageBox.Show("Nhập ngày tháng năm không hợp lệ!", AccessData.Msg, MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtTungay.Focus();
                return;
            }
		}

		private void frmBaoCaoThucLuc_Load(object sender, System.EventArgs e)
		{
			DateTime dt=DateTime.Now;			
            txtTungay.Text = dt.ToString("dd/MM/yyyy",null); 
		}

		private void butBaoCao_Click(object sender, System.EventArgs e)
		{
			if(radMH.Checked==true)	print(1);
			if(radMI.Checked==true) print(2);
            if(radExcel.Checked==true)print(3);
			butKetThuc.Focus();
		}

		private void txbTuNgay_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)		
				radMH.Focus();
		}

		private void radMH_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)		
				butBaoCao.Focus();
		}

		private void radMI_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)		
				butBaoCao.Focus();
		}

		private void radExcel_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)		
				butBaoCao.Focus();
		}	
	}
}
